IF OBJECT_ID('dbo.RptProgressReportDeliverableReport') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptProgressReportDeliverableReport
    IF OBJECT_ID('dbo.RptProgressReportDeliverableReport') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptProgressReportDeliverableReport >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptProgressReportDeliverableReport >>>'
END
go

-- =======================================================================      
-- Procedure Name: dbo.RptProgressReportDeliverableReport      
--      
-- Author:      Kevin Cashman, Parallax Consulting LLC      
--      
-- Create date: 10/03/2008      
--      
-- Description: Gets Counts of data of Financial Deliverables      
--      
-- Returns:     INT, 0=Success, -1=Error      
--      
--RptProgressReportDeliverableReport '05/05/2009','06/10/2009','Progress Report-Pending Out Years',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ORG',41359      
--------------------------------------------------------------------------      
-- Date        Initials  Modification      
--------------------------------------------------------------------------      
-- 04/20/09 RM Created based on RptSubcontractDeliverableReport      
--      
-- =======================================================================      
      
CREATE PROCEDURE dbo.RptProgressReportDeliverableReport      
     (@StartDate                        DATETIME,        
     @EndDate                           DATETIME,           
     @DeliverableStatus                 VARCHAR(5000) = NULL,      
     @Institution                       VARCHAR(100) = NULL,      
     @Department                        VARCHAR(100) = NULL,      
     @Unit                              VARCHAR(100) = NULL,      
     @FundNumber                        VARCHAR(1000) = NULL,      
     @PrincipalInvestigator             VARCHAR(100) = NULL,       
     @OriginatingSponsor                VARCHAR(100) = NULL,      
     @Sponsor                           VARCHAR(100) = NULL,           
     @SubcontractSpecialist             VARCHAR(100) = NULL,      
     @GroupBy                           VARCHAR(20) = NULL,      
     @DomainUserId                      INT     )       
  
  
  
  
                                              
AS      
BEGIN      
     -- Initial Settings      
     SET NOCOUNT ON      
     SET ANSI_WARNINGS OFF      
      
     -- Declare Local Variables      
     DECLARE   @Error              INT,      
               @RowCount           INT,      
               @ReturnCode         INT,      
      @DeliverableType  VARCHAR(200)      
      
 SET @DeliverableType = NULL                     
           
    DECLARE   @Proposals TABLE (      
                    FolderNumber             VARCHAR(12)    NULL,      
                    InfoEdPropNumber         VARCHAR(50)    NULL,      
                    Institution              VARCHAR(50)    NULL,      
                    Department               VARCHAR(50)    NULL,      
                    Unit                     VARCHAR(50)    NULL,      
                    ChiefCode                VARCHAR(4)     NULL,      
                    SponsorName              VARCHAR(75)    NULL,      
                    SponsorTypeName          VARCHAR(50)    NULL,      
                    SecondarySponsor         VARCHAR(75)    NULL,      
                    ProposalShortTitle       VARCHAR(64)    NULL,      
                    PrincipalInvestigator    VARCHAR(64)    NULL,      
                    ProjectStartDate         DATETIME       NULL,      
                    ProjectEndDate           DATETIME       NULL,   
					PreAwardManager          VARCHAR(75)    NULL,     
					PostAwardManager         VARCHAR(75)    NULL,       
                    InstrumentType           VARCHAR(50)    NULL,      
                    SubContractorName        VARCHAR(75)    NULL,      
                    BudgetProjectStartDate   DATETIME       NULL,      
                    BudgetProjectEndDate     DATETIME       NULL,      
                    ProjectAwardedAmount     MONEY          NULL,      
                    BudgetStartDate          DATETIME       NULL,      
                    BudgetEndDate            DATETIME       NULL,      
                    BudgetFundNumber         VARCHAR(20)    NULL,      
                    ExpenseAmount            MONEY          NULL,      
                    IndirectExpenses         MONEY          NULL,      
                    DirectExpenses           MONEY          NULL,      
                    CashBalance              MONEY          NULL,      
                    ReportId                 BIGINT         NULL,      
                    ReportName               VARCHAR(50)    NULL,      
                    REPORTCAT                VARCHAR(80)    NULL,      
                    REPORTSTAT  VARCHAR(80)    NULL,      
                    SubmittedDate            DATETIME       NULL,      
                    DueDate                  DATETIME       NULL,      
                    StatusDate               DATETIME       NULL,      
                    CompletedDate            DATETIME       NULL,      
                    DaysFromDueToCompleted   VARCHAR(5)     NULL,      
                    SubmittedBy              VARCHAR(50)    NULL,      
                    StatusBy                 VARCHAR(50)    NULL,      
                    CompletedBy              VARCHAR(50)    NULL,      
                    PeriodStartDate          DATETIME       NULL,      
                    PeriodEndDate            DATETIME       NULL,      
                    DeliverableComments      VARCHAR(2000)  NULL,      
                    REPORTCAT_CODE           VARCHAR(50)    NULL,      
                    REPORTSTAT_CODE          VARCHAR(50)    NULL,      
                    AgreementId              INT            NULL,      
                    InstitutionId            INT            NULL,      
                    DepartmentId             INT            NULL,      
                    UnitId                   INT            NULL,      
                    SponsorId                INT            NULL,   
					SponsorAgreementID       VARCHAR(50)    NULL,   
                    PrincipalInvestigatorId  INT            NULL,      
                    SubcontractsYN           CHAR(1)        NULL,      
                    SubContractorId          INT            NULL,      
                    SubOutContractorId       INT            NULL )      
      
     -- Let Detail Proc do the work.      
     INSERT    @Proposals      
                    (FolderNumber,      
                    InfoEdPropNumber,      
                    Institution,      
                    Department,      
                    Unit,      
                    ChiefCode,      
                    SponsorName,      
                    SponsorTypeName,      
                    SecondarySponsor,      
                    ProposalShortTitle,      
                    PrincipalInvestigator,      
                    ProjectStartDate,      
                    ProjectEndDate,      
					 PreAwardManager,    
					 PostAwardManager,   
                    InstrumentType,      
                    SubContractorName,      
                    BudgetProjectStartDate,      
                    BudgetProjectEndDate,      
                    ProjectAwardedAmount,      
                    BudgetStartDate,      
                    BudgetEndDate,      
                    BudgetFundNumber,      
                    ExpenseAmount,      
                    IndirectExpenses,      
                    DirectExpenses,      
                    CashBalance,      
                    ReportId,      
                    ReportName,      
                    REPORTCAT,      
                    REPORTSTAT,      
                    SubmittedDate,      
                    DueDate,      
                    StatusDate,      
                    CompletedDate,      
                    DaysFromDueToCompleted,      
                    SubmittedBy,      
                    StatusBy,      
                    CompletedBy,      
                    PeriodStartDate,      
                    PeriodEndDate,      
                    DeliverableComments,      
                    REPORTCAT_CODE,      
                    REPORTSTAT_CODE,      
                    AgreementId,      
                    InstitutionId,      
                    DepartmentId,      
                    UnitId,      
                    SponsorId,      
					SponsorAgreementID,
                    PrincipalInvestigatorId,      
                    SubcontractsYN,      
                    SubContractorId,      
                    SubOutContractorId)      
     EXECUTE @ReturnCode = dbo.RptProgressReportDeliverableDetailReport      
     @StartDate               = @StartDate,      
     @EndDate                 = @EndDate,                                    
     @DeliverableStatus       = @DeliverableStatus,   -- Get All      
     @Institution             = @Institution,      
     @Department              = @Department,      
     @Unit                    = @Unit,      
     @ChiefCode               = NULL,      
     @FundNumber              = @FundNumber,      
     @PrincipalInvestigator   = @PrincipalInvestigator,       
     @OriginatingSponsor      = @OriginatingSponsor,      
     @Sponsor                 = @Sponsor,                                    
     @SubcontractSpecialist   = @SubcontractSpecialist,      
     @SelectedHeaderStatus    = NULL,      
     @DomainUserId            = @DomainUserId      
                                    
     -- check for errors      
     SELECT @Error=@@ERROR      
     IF @Error != 0 OR @ReturnCode != 0      
     BEGIN      
          RETURN -1      
     END      
      
     -- We have to return some search parameter names so they can display in the headers.      
     -- Only if searching on one and only one value      
     IF @Institution IS NOT NULL AND CHARINDEX(',',@Institution) = 0      
          SELECT    TOP 1 @Institution = Institution      
          FROM      @Proposals      
     ELSE      
          SELECT    @Institution = NULL      
           
     IF @Department IS NOT NULL AND CHARINDEX(',',@Department) = 0      
          SELECT    TOP 1 @Department =Department      
          FROM      @Proposals      
     ELSE      
          SELECT    @Department = NULL      
      
     IF @Unit IS NOT NULL AND CHARINDEX(',',@Unit) = 0      
          SELECT    TOP 1 @Unit = Unit      
          FROM      @Proposals      
     ELSE      
          SELECT    @Unit = NULL      
      
     IF @PrincipalInvestigator IS NOT NULL AND CHARINDEX(',',@PrincipalInvestigator) = 0      
          SELECT    TOP 1 @PrincipalInvestigator = PrincipalInvestigator      
          FROM      @Proposals      
     ELSE      
          SELECT    @PrincipalInvestigator = NULL      
           
     IF @OriginatingSponsor IS NOT NULL AND CHARINDEX(',',@OriginatingSponsor) = 0      
          SELECT    TOP 1 @OriginatingSponsor = SecondarySponsor      
          FROM      @Proposals      
     ELSE      
          SELECT    @OriginatingSponsor = NULL      
      
     IF @Sponsor IS NOT NULL AND CHARINDEX(',',@Sponsor) = 0      
          SELECT    TOP 1 @Sponsor = SponsorName      
          FROM      @Proposals      
     ELSE      
          SELECT    @Sponsor = NULL      
      
     -- Return Result Set      
     SELECT    @DeliverableStatus As DeliverableStatus,      
      @Institution AS Institution,      
               @Department AS Department,      
               @Unit AS Unit,      
               @PrincipalInvestigator AS PrincipalInvestigator,      
               @OriginatingSponsor AS OriginatingSponsor,      
               @Sponsor AS Sponsor,      
               GroupBy,      
               GroupById1,      
               GroupByName1,      
               GroupById2,      
               GroupByName2,  
      GroupByName3,  
               SUM(CASE WHEN REPORTSTAT_CODE IN ('REPORTSTAT_43')       
                    THEN DeliverableCount      
                    ELSE NULL      
               END) AS AwaitingDept,      
               SUM(CASE WHEN REPORTSTAT_CODE IN ('REPORTSTAT_44')       
                    THEN DeliverableCount      
                    ELSE NULL      
               END) AS Complete,      
               SUM(CASE WHEN REPORTSTAT_CODE IN ('REPORTSTAT_45')       
                    THEN DeliverableCount      
                    ELSE NULL      
               END) AS PendingOutYears,      
               SUM(CASE WHEN REPORTSTAT_CODE NOT IN (  'REPORTSTAT_43','REPORTSTAT_44','REPORTSTAT_45')    
                    THEN DeliverableCount      
                    ELSE NULL      
               END) AS AllOtherUnCategorized      
     FROM      (      
               SELECT    @GroupBy AS GroupBy,      
                         CASE @GroupBy       
                              WHEN 'ORG' THEN ISNULL(InstitutionId,-1)     -- -1 means not set      
                              ELSE NULL      
                         END AS GroupById1,      
                         CASE @GroupBy       
         WHEN 'ORG' THEN ISNULL(Institution,'UNASSIGNED')      
                              ELSE NULL      
                         END AS GroupByName1,      
                         CASE @GroupBy       
                              WHEN 'ORG' THEN ISNULL(DepartmentId,-1)  -- -1 means not set      
                              ELSE -1      
                         END AS GroupById2,      
                         CASE @GroupBy       
                              WHEN 'ORG' THEN ISNULL(ChiefCode + ' - ' + Department,'UNASSIGNED')      
                              --WHEN 'SC' THEN ISNULL(SubContractorName,'UNASSIGNED')      
                              ELSE ISNULL(SubmittedBy,'UNASSIGNED')      
                         END AS GroupByName2,     
       CASE @GroupBy     
                              WHEN 'ORG' THEN ISNULL(ChiefCode,'0')    -- -1 means not set    
                              ELSE '0'    
                         END AS GroupByName3,                              
                         REPORTSTAT AS DeliverableStatus,      
                         REPORTSTAT_CODE,      
                         COUNT(ReportId) AS DeliverableCount      
               FROM      @Proposals      
               GROUP BY  CASE @GroupBy       
                              WHEN 'ORG' THEN ISNULL(InstitutionId,-1)           
                              ELSE NULL      
                         END,      
                         CASE @GroupBy       
                              WHEN 'ORG' THEN ISNULL(Institution,'UNASSIGNED')      
                              ELSE NULL      
                         END,      
                         CASE @GroupBy       
                              WHEN 'ORG' THEN ISNULL(DepartmentId,-1)  -- -1 means not set      
                              ELSE -1      
                         END,      
                         CASE @GroupBy       
                              WHEN 'ORG' THEN ISNULL(ChiefCode + ' - ' + Department,'UNASSIGNED')      
                              --WHEN 'SC' THEN ISNULL(SubContractorName,'UNASSIGNED')      
                              ELSE ISNULL(SubmittedBy,'UNASSIGNED')      
                         END,               
       CASE @GroupBy     
                              WHEN 'ORG' THEN ISNULL(ChiefCode,'0')    -- -1 means not set    
                              ELSE '0'    
                         END,                  
                         REPORTSTAT,      
                         REPORTSTAT_CODE ) DC      
     GROUP BY  GroupBy,      
               GroupById1,      
               GroupByName1,      
               GroupById2,  
               GroupByName2,  
      GroupByName3  
     ORDER BY  GroupBy,      
               GroupByName1,      
               GroupByName2      
                     
     -- check for errors      
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT      
     IF @Error != 0       
     BEGIN      
          RETURN -1      
     END           
           
     -- Return Success      
     RETURN 0      
           
END 
go

go

IF OBJECT_ID('dbo.RptProgressReportDeliverableReport') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptProgressReportDeliverableReport >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptProgressReportDeliverableReport >>>'
go

GRANT EXECUTE ON dbo.RptProgressReportDeliverableReport TO InsightReport,Insight
go

